﻿

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using MySql.Data.MySqlClient;

public class DAL_tbl_siconideblending
{
    public DAL_tbl_siconideblending()
    {

    }



    public List<tbl_siconideblending> select(int ID)
    {
        List<tbl_siconideblending> list = new List<tbl_siconideblending>();
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "Select ID,Blending_Date,Seed_Drum,Charge_Number,Notes,IS_ACTIVE,CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE from tbl_siconideblending where ID=@ID";
        cmd.Parameters.AddWithValue("@ID", ID);
        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        foreach (DataRow dr in dt.Rows)
        {
            list.Add(new tbl_siconideblending()
            {
                ID = Convert.ToInt32(dr["ID"]),
                Blending_Date = dr["Blending_Date"].toDateTimeWithoutError(),
                Seed_Drum = dr["Seed_Drum"].ToString(),
                Charge_Number = Convert.ToDecimal(dr["Charge_Number"]),
                Notes = dr["Notes"].ToString(),
                IS_ACTIVE = Convert.ToBoolean(dr["IS_ACTIVE"]),
                CREATED_BY = dr["CREATED_BY"].ToString(),
                CREATED_DATE = dr["CREATED_DATE"].toDateTimeWithoutError(),
                UPDATED_BY = dr["UPDATED_BY"].ToString(),
                UPDATED_DATE = dr["UPDATED_DATE"].toDateTimeWithoutError(),
                siconideblending_vessels=getsiconideblending_vessels(Convert.ToInt32(dr["ID"]))
            });
        }

        return list;
    }


    private List<tbl_siconideblending_drums> getsiconideblending_vessels(int SicoNideBlending_id)
    {
        List<tbl_siconideblending_drums> list = new List<tbl_siconideblending_drums>();
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "Select ID, tbl_SicoNideBlending_id, Drum_Bag, Drum_Bag_Number from tbl_siconideblending_drums where tbl_SicoNideBlending_id=@tbl_SicoNideBlending_id";
        cmd.Parameters.AddWithValue("@tbl_SicoNideBlending_id", SicoNideBlending_id);
        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        foreach (DataRow dr in dt.Rows)
        {
            list.Add(new tbl_siconideblending_drums()
            {
                tbl_SicoNideBlending_id = Convert.ToInt32(dr["tbl_SicoNideBlending_id"]),
                Drum_Bag = dr["Drum_Bag"].ToString(),
                Drum_Bag_Number = dr["Drum_Bag_Number"].ToString()
            });
        }

        return list;
    }



    public List<tbl_siconideblending> selectAll()
    {
        List<tbl_siconideblending> list = new List<tbl_siconideblending>();
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "Select ID,Blending_Date,Seed_Drum,Charge_Number,Notes,IS_ACTIVE,CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE from tbl_siconideblending Order By ID";

        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        foreach (DataRow dr in dt.Rows)
        {
            list.Add(new tbl_siconideblending()
            {
                ID = Convert.ToInt32(dr["ID"]),
                Blending_Date = dr["Blending_Date"].toDateTimeWithoutError(),
                Seed_Drum = dr["Seed_Drum"].ToString(),
                Charge_Number = Convert.ToDecimal(dr["Charge_Number"]),
                Notes = dr["Notes"].ToString(),
                IS_ACTIVE = Convert.ToBoolean(dr["IS_ACTIVE"]),
                CREATED_BY = dr["CREATED_BY"].ToString(),
                CREATED_DATE = dr["CREATED_DATE"].toDateTimeWithoutError(),
                UPDATED_BY = dr["UPDATED_BY"].ToString(),
                UPDATED_DATE = dr["UPDATED_DATE"].toDateTimeWithoutError(),
                siconideblending_vessels=getsiconideblending_vessels(Convert.ToInt32(dr["ID"]))
            });
        }

        return list;
    }

    public DataTable selectAllForGrid()
    {
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = @"Select ID,Blending_Date as Blending_Date,Seed_Drum,Charge_Number,Notes,CASE WHEN IS_ACTIVE =1 THEN 'True' ELSE 'False' END as 'IS_ACTIVE',CREATED_BY,CREATED_DATE as CREATED_DATE,UPDATED_BY,UPDATED_DATE as UPDATED_DATE from tbl_siconideblending Order By ID desc";

        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        return dt;
    }

    public int insert(DateTime Blending_Date, String Seed_Drum, Decimal Charge_Number, String Notes, String CREATED_BY, DateTime CREATED_DATE, List<tbl_siconideblending_drums> list_siconideblending_vessels)
    {
        int i = 0;
        string sql = @"Insert into tbl_siconideblending(Blending_Date, Seed_Drum, Charge_Number, Notes, CREATED_BY, CREATED_DATE)
                        values(@Blending_Date, @Seed_Drum, @Charge_Number, @Notes, @CREATED_BY, @CREATED_DATE)";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@Blending_Date", Blending_Date);
        cmd.Parameters.AddWithValue("@Seed_Drum", Seed_Drum);
        cmd.Parameters.AddWithValue("@Charge_Number", Charge_Number);
        cmd.Parameters.AddWithValue("@Notes", Notes);
        cmd.Parameters.AddWithValue("@CREATED_BY", HttpContext.Current.User.Identity.Name);
        cmd.Parameters.AddWithValue("@CREATED_DATE", DateTime.Now);

        i = DBUtil.ExecuteNonQuery(cmd);

        string sqltxt = "select ifnull(Max(ID),0) from tbl_siconideblending";
        cmd.CommandText = sqltxt;
        cmd.Parameters.Clear();
        int SicoNideBlending_id = Convert.ToInt32(DBUtil.ExecuteScalar(cmd));

        insert_siconideblending_vessels(list_siconideblending_vessels, SicoNideBlending_id);
        return i;
    }


    private void insert_siconideblending_vessels(List<tbl_siconideblending_drums> list, int SicoNideBlending_id)
    {
        string sql = @"Insert into tbl_siconideblending_drums(tbl_SicoNideBlending_id, Drum_Bag, Drum_Bag_Number)
                        values(@tbl_SicoNideBlending_id, @Drum_Bag, @Drum_Bag_Number)";

        MySqlCommand cmd = new MySqlCommand(sql);

        foreach (tbl_siconideblending_drums item in list)
        {
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@tbl_SicoNideBlending_id", SicoNideBlending_id);
            cmd.Parameters.AddWithValue("@Drum_Bag", item.Drum_Bag);
            cmd.Parameters.AddWithValue("@Drum_Bag_Number", item.Drum_Bag_Number);
            DBUtil.ExecuteNonQuery(cmd);
        }
    }


    public int update(int ID, DateTime Blending_Date, String Seed_Drum, Decimal Charge_Number, String Notes, String UPDATED_BY, DateTime UPDATED_DATE, List<tbl_siconideblending_drums> list_siconideblending_vessels)
    {
        int i = 0;
        string sql = @"Update tbl_siconideblending SET Blending_Date=@Blending_Date, Seed_Drum=@Seed_Drum, Charge_Number=@Charge_Number, Notes=@Notes, UPDATED_BY=@UPDATED_BY, UPDATED_DATE=@UPDATED_DATE WHERE ID=@ID";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@ID", ID);
        cmd.Parameters.AddWithValue("@Blending_Date", Blending_Date);
        cmd.Parameters.AddWithValue("@Seed_Drum", Seed_Drum);
        cmd.Parameters.AddWithValue("@Charge_Number", Charge_Number);
        cmd.Parameters.AddWithValue("@Notes", Notes);
        cmd.Parameters.AddWithValue("@UPDATED_BY", HttpContext.Current.User.Identity.Name);
        cmd.Parameters.AddWithValue("@UPDATED_DATE", DateTime.Now);

        i = DBUtil.ExecuteNonQuery(cmd);

        int SicoNideBlending_id = ID;
        update_siconideblending_vessels(list_siconideblending_vessels, SicoNideBlending_id);
        return i;
    }


    private void update_siconideblending_vessels(List<tbl_siconideblending_drums> list, int SicoNideBlending_id)
    {
        BLL_tbl_siconideblending bll = new BLL_tbl_siconideblending();
        DataTable dt = bll.getDataSiconideblending_vessels(SicoNideBlending_id);

        int i = 0;
        foreach (tbl_siconideblending_drums item in list)
        {

            string sql = "Update tbl_siconideblending_drums set Drum_Bag=@Drum_Bag, Drum_Bag_Number=@Drum_Bag_Number where tbl_SicoNideBlending_id=@tbl_SicoNideBlending_id and ID="+ dt.Rows[i]["ID"];

            MySqlCommand cmd = new MySqlCommand(sql);
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@tbl_SicoNideBlending_id", SicoNideBlending_id);
            cmd.Parameters.AddWithValue("@Drum_Bag", item.Drum_Bag);
            cmd.Parameters.AddWithValue("@Drum_Bag_Number", item.Drum_Bag_Number);
            DBUtil.ExecuteNonQuery(cmd);
            i++;
        }
    }


    public int delete(int ID, List<tbl_siconideblending_drums> list_siconideblending_vessels)
    {
        int i = 0;
        string sql = "Delete FROM tbl_siconideblending WHERE ID=@ID";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@ID", ID);

        i = DBUtil.ExecuteNonQuery(cmd);
        delete_siconideblending_vessels(list_siconideblending_vessels, ID);
        return i;
    }


    private void delete_siconideblending_vessels(List<tbl_siconideblending_drums> list, int SicoNideBlending_id)
    {
        string sql = "delete tbl_siconideblending_drums where tbl_SicoNideBlending_id=@tbl_SicoNideBlending_id";

        MySqlCommand cmd = new MySqlCommand(sql);

        foreach (tbl_siconideblending_drums item in list)
        {
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@tbl_SicoNideBlending_id", SicoNideBlending_id);
            DBUtil.ExecuteNonQuery(cmd);
        }
    }

    public int countsiconideblending_vessels(int SicoNideBlending_id)
    {
        int i = 0;
        string sql = @"SELECT count(1) FROM tbl_siconideblending_drums where tbl_SicoNideBlending_id=@tbl_SicoNideBlending_id";
        MySqlCommand cmd = new MySqlCommand(sql);
        cmd.Parameters.AddWithValue("@tbl_SicoNideBlending_id", SicoNideBlending_id);
        i = Convert.ToInt32(DBUtil.ExecuteScalar(cmd));
        return i;

    }

    public DataTable getDataSiconideblending_vessels(int SicoNideBlending_id)
    {
        string sql = @"SELECT * FROM tbl_siconideblending_drums where tbl_SicoNideBlending_id=@tbl_SicoNideBlending_id";
        MySqlCommand cmd = new MySqlCommand(sql);
        cmd.Parameters.AddWithValue("@tbl_SicoNideBlending_id", SicoNideBlending_id);
        try
        {
            return DBUtil.ExecuteDataTable(cmd);
        }
        finally
        {

            cmd = null;
        }
    }

}
